/*
 * @(#)SqlCommand.java
 *
 * @author      Orlin Tomov
 * 
 */

package dbu.sql;

import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.Vector;

/**
 * A class implementing independent SQL command preprocessor
 */
public class SqlCommand {
	public static final String INSERT = "INSERT";
	public static final String UPDATE = "UPDATE";
	public static final String DELETE = "DELETE";
	private static final String SPACE = " ";
	private static final String COMMA = ",";
	private static final String SELECT = "SELECT";
	private static final String _FROM_ = " FROM ";
	private static final String INTO = "INTO";
	private static final String CREATE = "CREATE";
	private static final String TABLE = "TABLE";
	private static final String _SET_ = " SET ";
	private static final String _VALUES = " VALUES";
	private static final String _WHERE_ = " WHERE ";
	private static final String _ORDER_ = " ORDER ";
	private static final String _BY_ = " BY ";

	private String sql;
	private String cmd = null;
	private String tableName = null;
	private Collection<String> columns = null;
	private Collection<String> values = null;
	private String where = null;
	private String orderBy = null;

	public SqlCommand(String sql) {
		this.sql = sql.trim();
	}

	public String getCmd() {
		if (cmd == null) {
			cmd = sql.substring(0, sql.indexOf(SPACE)).toUpperCase();
		}
		return cmd;
	}

	public String getTableName() {
		if (tableName == null) {
			if (getUp().startsWith(SELECT)) {
				tableName = wordAfter(sql, _FROM_).toLowerCase();
			} else if (getUp().startsWith(INSERT)) {
				tableName = wordAfter(sql, INTO).toLowerCase();
			} else if (getUp().startsWith(UPDATE)) {
				tableName = wordAfter(sql, UPDATE).toLowerCase();
			} else if (getUp().startsWith(DELETE)) {
				tableName = wordAfter(sql, _FROM_).toLowerCase();
			} else if (getUp().startsWith(CREATE)) {
				tableName = wordAfter(sql, TABLE).toLowerCase();
			}
		}
		return tableName;
	}

	public Collection<String> getColumns() {
		if (columns == null) {
			int tableNameEnds = sql.indexOf(tableName) + tableName.length();
			if (getCmd().equals(CREATE)) {
				columns = splitFields(sql.substring(tableNameEnds).trim().toLowerCase());
			} else if (getCmd().equals(SELECT)) {
				int fromIndex = getUp().indexOf(_FROM_);
				columns = splitFields(sql.substring(SELECT.length(), fromIndex).trim().toLowerCase());
			} else if (getCmd().equals(INSERT)) {
				int columnsEnd = getUp().indexOf(_VALUES);
				columns = splitFields(sql.substring(tableNameEnds, columnsEnd).trim().toLowerCase());
			} else if (getCmd().equals(UPDATE)) {
				int setIndex = getUp().indexOf(_SET_);
				if (setIndex > -1) {
					setIndex += _SET_.length();
				}
				int whereIndex = getUp().indexOf(_WHERE_);
				if (whereIndex > -1) {
					whereIndex += _VALUES.length();
				}
				String sets = sql.substring(setIndex, whereIndex - _WHERE_.length()).trim();
				Map<String, String> tokens = splitFields(splitFields(sets));
				columns = tokens.keySet();
				values = tokens.values();
			}
		}
		return columns;
	}

	public Collection<String> getValues() {
		if (values == null) {
			if (getCmd().equals(INSERT)) {
				int valuesIndex = getUp().indexOf(_VALUES);
				if (valuesIndex > -1) {
					valuesIndex += _VALUES.length();
				}
				values = splitFields(sql.substring(valuesIndex).trim());
			} else if (getCmd().equals(UPDATE)) {
				int setIndex = getUp().indexOf(_SET_);
				if (setIndex > -1) {
					setIndex += _SET_.length();
				}
				int whereIndex = getUp().indexOf(_WHERE_);
				if (whereIndex > -1) {
					whereIndex += _VALUES.length();
				}
				String sets = sql.substring(setIndex, whereIndex - _WHERE_.length()).trim();
				Map<String, String> tokens = splitFields(splitFields(sets));
				columns = tokens.keySet();
				values = tokens.values();
			}
		}
		return values;
	}

	public String getWhere() {
		if (where == null) {
			if (getCmd().equals(SELECT) || getCmd().equals(UPDATE) || getCmd().equals(DELETE)) {
				int whereIndex = getUp().indexOf(_WHERE_);
				if (whereIndex > -1) {
					whereIndex += _VALUES.length();
				}
				int orderIndex = getUp().indexOf(_ORDER_);
				if (whereIndex > -1) {
					if (orderIndex > -1) {
						where = sql.substring(whereIndex, orderIndex);
					} else {
						where = sql.substring(whereIndex);
					}
					where = getTokens(splitFields(where.trim())); /** undouble quotes */
				}
			}
		}
		return where;
	}

	public String getOrderBy() {
		if (orderBy == null) {
			if (getCmd().equals(SELECT)) {
				int orderIndex = getUp().indexOf(_ORDER_);
				if (orderIndex > -1) {
					int orderByIndex = getUp().indexOf(_BY_, orderIndex);
					orderBy = sql.substring(orderByIndex).trim();
				}
			}
		}
		return orderBy;
	}

	public String getTokens(Collection<String> tokens) {
		StringBuffer result = new StringBuffer();
		for (String token: tokens) {
			if (result.length() > 0) {
				result.append(COMMA);
				result.append(SPACE);
			}
			result.append(token);
		}
		return result.toString();
	}

	private String getUp() {
		return sql.toUpperCase();
	}

	private Map<String, String> splitFields(Collection<String> tokens) {
		Map<String, String> result = new LinkedHashMap<String, String>();
		for (String token: tokens) {
			 String key = token.substring(0, token.indexOf(Operator.EQ.toString())).trim().toLowerCase();
			 String val = token.substring(token.indexOf(Operator.EQ.toString()) + Operator.EQ.toString().length()).trim();
			 result.put(key, val);
		}
		return result;
	}

	private Collection<String> splitFields(String fields) {
		Collection<String> tokens = new Vector<String>();
		fields = fields.trim();
		if (fields.startsWith("(")) {
			fields = fields.substring(1);
		}
		if (fields.endsWith(")")) {
			fields = fields.substring(0, fields.length() - 1);
		}
		String subTest = "";
		int commas = 0;
		StringTokenizer st = new StringTokenizer(fields, "',", true);
		while (st.hasMoreTokens()) {
			String token = st.nextToken();
			if (token.equals("'")) {
				if (!subTest.endsWith("'") || ((commas % 2) != 0)) {
					/** in order to add only first of a pair quotes */
					subTest += token;
				}
				commas++;
			} else if (token.equals(",") && ((commas % 2) == 0)) {
				subTest = subTest.trim();
				tokens.add(subTest);
				subTest = "";
				commas = 0;
			} else {
				subTest += token;
			}
		}
		if (!subTest.isEmpty()) {
			tokens.add(subTest);
		}
		return tokens;
	}

	private String wordAfter(String sql, String after) {
		String word = sql.substring(sql.toUpperCase().indexOf(after) + after.length()).trim();
		if (word.indexOf(SPACE) > -1) {
			word = word.substring(0, word.indexOf(SPACE));
		}
		return word.trim();
	}

	public Query getQuery() {
		Query query = new Query(getWhere());
		return query;
	}
}
